package com.ivo.service.pdm;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.transaction.annotation.Transactional;

import com.ivo.dao.HibernateCaller;
import com.ivo.model.Attachment;
import com.ivo.model.DataType;
import com.ivo.model.Dependency;
import com.ivo.model.Log;
import com.ivo.model.MasterAttributeInstance;
import com.ivo.model.MasterInstance;
import com.ivo.model.Template;
import com.ivo.model.TemplateAttribute;

public class SheetService extends HibernateCaller
{
	/**
	 * Sheet
	 */
	@Transactional
	public void saveTmplAttr(TemplateAttribute tmplAttr)
	{
		saveObject( tmplAttr );
	}

	public void saveTmpl(Template tmpl)
	{
		saveObject( tmpl );
	}

	public void saveAttachment(Attachment attachment)
	{
		saveObject( attachment );
	}

	public void saveMasterInstance(MasterInstance masterInstance)
	{
		saveObject( masterInstance );
	}

	public void saveDependency(Dependency dependency)
	{
		saveObject( dependency );
	}

	public void saveLog(Log log)
	{
		saveObject( log );
	}

	public void updateTmpl(Template tmpl)
	{
		updateObject( tmpl );
	}

	public void updateTmplAttr(TemplateAttribute tmplAttr)
	{
		updateObject( tmplAttr );
	}

	public void updateMasterInstance(MasterInstance masterInstance)
	{
		updateObject( masterInstance );
	}

	public void updateDependency(Dependency dependency)
	{
		updateObject( dependency );
	}

	public TemplateAttribute getTmplAttr(Long tmplAttr_Id)
	{
		TemplateAttribute tmplAttr = (TemplateAttribute) getObject( TemplateAttribute.class, tmplAttr_Id );
		return tmplAttr;
	}

	public Template getTmpl(Long tmpl_Id)
	{
		Template tmpl = (Template) getObject( Template.class, tmpl_Id );
		return tmpl;
	}

	public Attachment getAttachment(Long attachmentId)
	{
		Attachment attachment = (Attachment) getObject( Attachment.class, attachmentId );
		return attachment;
	}

	public DataType getDataType(String dataType_Id)
	{
		DataType dataType = (DataType) getObject( DataType.class, dataType_Id );
		return dataType;
	}

	public MasterInstance getMasterInstance(Long mastInsId)
	{
		MasterInstance masterInstance = (MasterInstance) getObject( MasterInstance.class, mastInsId );
		return masterInstance;
	}

	public MasterAttributeInstance getMasterAttributeInstance(Long mastAttrInsId)
	{
		MasterAttributeInstance masterAttributeInstance = (MasterAttributeInstance) getObject( MasterAttributeInstance.class, mastAttrInsId );
		return masterAttributeInstance;
	}

	public List<TemplateAttribute> getTmplAttrs(Long tmpl_Id)
	{
		String hql = "from TemplateAttribute a where a.template.templateId = ? and a.isValid = 1 order by a.sortOrder";
		List list = get( hql, tmpl_Id );
		return list;
	}

	public TemplateAttribute getTmplAttrByName(Long tmpl_Id, String attrName)
	{
		String hql = "from TemplateAttribute a where a.template.templateId = ? and a.attributeName = ? and a.isValid = 1 order by a.sortOrder";
		List list = get( hql, tmpl_Id, attrName );

		TemplateAttribute tmplAttr = null;
		if( list.size() > 0 ) tmplAttr = (TemplateAttribute) list.get( 0 );

		return tmplAttr;
	}

	public List<TemplateAttribute> getTmplReferenceAttrs(Long tmpl_Id)
	{
		String hql = "from TemplateAttribute a where a.template.templateId = ? and a.isValid = 1 and a.dataType.typeId in ('DT022','DT016','DT011','DT009','DT010','DT024','DT006') order by a.sortOrder";
		List list = get( hql, tmpl_Id );
		return list;
	}

	public List<Dependency> getTmplAttrDependency(Long attributeId)
	{
		String hql = "from Dependency a where a.templateAttribute.attributeId = ? and a.isValid = 1 and a.masterIntance.instanceId is null";
		List list = find( hql, attributeId );
		return list;
	}

	public List<Dependency> getRefTmplAttrs(Long instanceId)
	{
		String hql = "from Dependency a where a.masterIntance.instanceId = ? and a.isValid = 1 and a.isTmpl = 0 order by a.templateAttribute.attributeId ";
		List list = find( hql, instanceId );
		return list;
	}

	public List<TemplateAttribute> getTmplAttrsByType(Long tmpl_Id, String typeId)
	{
		String hql = "from TemplateAttribute a where a.template.templateId = ? and a.dataType.typeId = ? and a.isValid = 1 order by a.sortOrder";
		List list = get( hql, tmpl_Id, typeId );
		return list;
	}

	public boolean hasTmplAttr(Long tmpl_Id, String attr_id)
	{
		boolean repeat = false;
		String hql = "from TemplateAttribute a where a.template.templateId = ? and a.attributeName = ? and a.isValid = 1 order by a.sortOrder";
		List list = get( hql, tmpl_Id, attr_id );
		if( list.size() > 0 ) repeat = true;
		return repeat;
	}

	public boolean hasTmplAttr_Double(Long tmpl_Id, String attr_id)
	{
		boolean repeat = false;
		String hql = "from TemplateAttribute a where a.template.templateId = ? and a.attributeName = ? and a.isValid = 1 order by a.sortOrder";
		List list = get( hql, tmpl_Id, attr_id );
		if( list.size() > 1 ) repeat = true;
		return repeat;
	}

	public List<TemplateAttribute> getGroupAttrs(Long tmpl_Id, Integer group_Id)
	{
		String hql = "from TemplateAttribute a where a.template.templateId = ? and a.groupNumber = ? and a.isValid = 1 order by a.sortOrder";
		List list = get( hql, tmpl_Id, group_Id );
		return list;
	}

	public List<DataType> getParentDataType()
	{
		String hql = "from DataType dt where dt.parent is null and dt.isValid = 1";
		List list = find( hql );
		return list;
	}

	public List<DataType> getSubDataType()
	{
		String hql = "from DataType dt where dt.parent is not null and dt.isValid = 1";
		List list = find( hql );
		return list;
	}

	public DataType getDataTypeById(String cateId)
	{
		String hql = "from DataType dt where dt.typeId = ? and dt.isValid = 1 order by dt.parent.typeId ";
		List list = find( hql, cateId );
		DataType dataType = null;
		if( list.size() > 0 ) dataType = (DataType) list.get( 0 );
		return dataType;
	}

	public List<MasterAttributeInstance> getMastAttrIns(Long mastInsId)
	{
		String hql = "from MasterAttributeInstance a where a.masterInstance.instanceId = ? and a.isValid = 1";
		List list = get( hql, mastInsId );
		return list;
	}

	public List<MasterAttributeInstance> getMastAttrIns(Long mastInsId, Long tmplAttrId)
	{
		String hql = "from MasterAttributeInstance a where a.masterInstance.instanceId = ? and a.templateAttribute.attributeId = ? and a.isValid = 1";
		List list = get( hql, mastInsId, tmplAttrId );
		return list;
	}

	public MasterInstance getMasterInsByCateId(Long cateId)
	{
		String hql = "from MasterInstance a where a.category.cateId = ? and a.isValid = 1";
		List list = get( hql, cateId );
		MasterInstance mastIns = null;
		if( list.size() > 0 ) mastIns = (MasterInstance) list.get( 0 );

		return mastIns;
	}

	public HashMap<String, String> getMastAttrInsMap(MasterInstance masterInstance)
	{
		HashMap<String, String> mapValues = new HashMap<String, String>();
		List<MasterAttributeInstance> mastAttrIns = masterInstance.getMastAttrIns();

		for( MasterAttributeInstance mastAttr : mastAttrIns ) {
			TemplateAttribute tmplAttr = mastAttr.getTemplateAttribute();
			String attrId = tmplAttr.getAttributeId() + "";
			String attrValue = mastAttr.getValue();
			mapValues.put( attrId, attrValue );
		}

		return mapValues;
	}

	public void copyTemplateAttributeByTargetId(Long targetId, Long tmplId, String userId)
	{
		String sql = " insert into PDM_D_TMPL_ATTR(TMPL_ATTR_NAME,ATTR_LABEL_TEXT,TMPL_FK,DATA_TYPE_FK,DEFT_VALUE,DATA_SOURCE,LENGTH,FORMAT,SEPERATOR_FLAG,DECIMAL_DIGITS,COL_SPAN,GROUP_NUMBER,SORT_ORDER,IS_VALID,CREATOR,UPDATER,CREATE_DATE,UPDATE_DATE,MEMO) "
				+ " select TMPL_ATTR_NAME,ATTR_LABEL_TEXT,? AS TMPL_FK,DATA_TYPE_FK,DEFT_VALUE,DATA_SOURCE,LENGTH,FORMAT,SEPERATOR_FLAG,DECIMAL_DIGITS,COL_SPAN,GROUP_NUMBER,SORT_ORDER,IS_VALID, ? AS CREATOR,? AS UPDATER,getdate() AS CREATE_DATE, getdate() AS UPDATE_DATE, '' AS MEMO "
				+ " from PDM_D_TMPL_ATTR " + " WHERE TMPL_FK = ? " + " AND IS_VALID = 1 ";

		sqlNonQuery( sql, targetId, userId, userId, tmplId );
	}

	public void checkAndCreateMastAttrIns(String mastInsId, String tmplId)
	{
		String sql = " exec PDM_SP_CheckMasterAttributeInstance ?,? ";

		sqlNonQuery( sql, mastInsId, tmplId );
	}

	public List<Attachment> getAttachments(Long mastInsId)
	{
		String hql = "from Attachment a where a.mastIns.instanceId = ? and a.isValid = 1";
		List list = find( hql, mastInsId );
		return list;
	}

	public boolean getDocumentAuthority(Long mastInsId, String userId)
	{
		StringBuffer sbSQL = new StringBuffer();
		boolean bAuth = false;

		sbSQL.append( " exec PDM_SP_DocumentAuthority " );
		sbSQL.append( mastInsId );
		sbSQL.append( " , " );
		sbSQL.append( userId );
		List list = sqlQuery( sbSQL.toString(), "AUTH" );

		Map map = (Map) list.get( 0 );
		bAuth = map.get( "AUTH" ).equals( "true" );

		return bAuth;
	}

	public String getDivDisplayType(Long tmpl_Id)
	{
		StringBuffer sbSQL = new StringBuffer();
		String strReturn = "0000";

		sbSQL.append( " declare @DIV_TYPE nvarchar(4); " );
		sbSQL.append( " if exists ( " );
		sbSQL.append( " select * from PDM_D_TMPL_ATTR " );
		sbSQL.append( " where TMPL_FK = " );
		sbSQL.append( tmpl_Id );
		sbSQL.append( " and IS_VALID = 1 and DATA_TYPE_FK = 'DT018' " );
		sbSQL.append( " ) begin set @DIV_TYPE = '1'; end " );
		sbSQL.append( " else begin set @DIV_TYPE = '0'; end " );
		sbSQL.append( " if exists ( " );
		sbSQL.append( " select * from PDM_D_TMPL_ATTR " );
		sbSQL.append( " where TMPL_FK = " );
		sbSQL.append( tmpl_Id );
		sbSQL.append( " and IS_VALID = 1 and DATA_TYPE_FK = 'DT019' " );
		sbSQL.append( " ) begin set @DIV_TYPE = @DIV_TYPE + '1'; end " );
		sbSQL.append( " else begin set @DIV_TYPE = @DIV_TYPE + '0'; end " );
		sbSQL.append( " if exists ( " );
		sbSQL.append( " select * from PDM_D_TMPL_ATTR " );
		sbSQL.append( " where TMPL_FK = " );
		sbSQL.append( tmpl_Id );
		sbSQL.append( " and IS_VALID = 1 and DATA_TYPE_FK = 'DT021' " );
		sbSQL.append( " ) begin set @DIV_TYPE = @DIV_TYPE + '1'; end " );
		sbSQL.append( " else begin set @DIV_TYPE = @DIV_TYPE + '0'; end " );

		sbSQL.append( " select @DIV_TYPE as DIV_TYPE " );

		List list = sqlQuery( sbSQL.toString(), "DIV_TYPE" );

		Map map = (Map) list.get( 0 );
		strReturn = map.get( "DIV_TYPE" ) + "";
		return strReturn;
	}

	public List<MasterAttributeInstance> getRefMasterAttributeInstances(long mastAttrInsId)
	{

		StringBuffer sbSql = new StringBuffer();

		sbSql.append( " SELECT MAS.* " );
		sbSql.append( " FROM PDM_D_MAST_ATTR_INS AS MAS inner join ( " );
		sbSql.append( " SELECT DPDN.REF_MAST_INS_FK,DPDN.REF_TMPL_ATTR_FK " );
		sbSql.append( " FROM PDM_D_DEPENDENCY AS DPDN INNER JOIN PDM_D_MAST_ATTR_INS AS MAI " );
		sbSql.append( " ON DPDN.MAST_INS_FK = MAI.MAST_INS_FK " );
		sbSql.append( " AND DPDN.TMPL_ATTR_FK = MAI.TMPL_ATTR_FK AND DPDN.MAST_INS_FK = ? " );
		sbSql.append( " ) AS TBL " );
		sbSql.append( " ON MAS.MAST_INS_FK = TBL.REF_MAST_INS_FK " );
		sbSql.append( " AND MAS.TMPL_ATTR_FK = TBL.REF_TMPL_ATTR_FK " );

		List list = sqlQuery( sbSql.toString(), MasterAttributeInstance.class, mastAttrInsId );
		return list;
	}

	public HashMap<String, String> getRefAttrInsMap(Long masterInstanceId)
	{
		HashMap<String, String> mapValues = new HashMap<String, String>();

		List<Dependency> refDependency = this.getRefTmplAttrs( masterInstanceId );

		for( int i = 0; i < refDependency.size(); i++ ) {
			Dependency dependency = refDependency.get( i );

			long nTmplAttrId = dependency.getTemplateAttribute().getAttributeId();
			long nRefMastInsId = dependency.getRefMasterIntance().getInstanceId();
			long nRefTmplAttrId = dependency.getRefTemplateAttribute().getAttributeId();
			String value = "";

			List<MasterAttributeInstance> mastAttrInss = this.getMastAttrIns( nRefMastInsId, nRefTmplAttrId );

			int mastSize = 0;
			mastSize = mastAttrInss.size();
			if( mastSize > 0 ) {
				MasterAttributeInstance mastAttrIns = mastAttrInss.get( mastSize - 1 );
				value = mastAttrIns.getValue();
			}

			mapValues.put( nTmplAttrId + "", value );
		}

		return mapValues;
	}

	public HashMap<String, String> getRefAttrMap(Long masterInstanceId)
	{
		HashMap<String, String> mapValues = new HashMap<String, String>();
		StringBuffer sbSQL = new StringBuffer();

		sbSQL.append( " SELECT D.MAST_INS_FK, D.TMPL_ATTR_FK, D.REF_MAST_INS_FK, D.REF_TMPL_ATTR_FK, MAI.VALUE AS ATTR_VALUE " );
		sbSQL.append( " FROM PDM_D_DEPENDENCY AS D INNER JOIN PDM_D_MAST_ATTR_INS AS MAI " );
		sbSQL.append( " ON D.MAST_INS_FK = " );
		sbSQL.append( masterInstanceId );
		sbSQL.append( " AND D.IS_VALID = 1 AND D.REF_MAST_INS_FK = MAI.MAST_INS_FK AND D.REF_TMPL_ATTR_FK = MAI.TMPL_ATTR_FK AND MAI.IS_VALID = 1 " );
		sbSQL.append( " INNER JOIN PDM_M_MAST_INS AS MI ON MI.MAST_INS_ID = MAI.MAST_INS_FK AND MI.IS_VALID = 1 " );
		sbSQL.append( " INNER JOIN PDM_O_CATE AS C ON MI.CATE_FK = C.CATE_ID AND C.IS_REQ = 1 AND C.IS_VALID = 1 " );
		sbSQL.append( " UNION ALL " );
		sbSQL.append( " SELECT D.MAST_INS_FK, D.TMPL_ATTR_FK, D.REF_MAST_INS_FK, D.REF_TMPL_ATTR_FK, 'NA' AS ATTR_VALUE " );
		sbSQL.append( " FROM PDM_D_DEPENDENCY AS D INNER JOIN PDM_D_MAST_ATTR_INS AS MAI " );
		sbSQL.append( " ON D.MAST_INS_FK = " );
		sbSQL.append( masterInstanceId );
		sbSQL.append( " AND D.IS_VALID = 1 AND D.REF_MAST_INS_FK = MAI.MAST_INS_FK AND D.REF_TMPL_ATTR_FK = MAI.TMPL_ATTR_FK AND MAI.IS_VALID = 1 " );
		sbSQL.append( " INNER JOIN PDM_M_MAST_INS AS MI ON MI.MAST_INS_ID = MAI.MAST_INS_FK AND MI.IS_VALID = 1 " );
		sbSQL.append( " INNER JOIN PDM_O_CATE AS C ON MI.CATE_FK = C.CATE_ID AND C.IS_REQ = 0 AND C.IS_VALID = 1 " );

		List list = sqlQuery( sbSQL.toString() );

		for( int i = 0; i < list.size(); i++ ) {
			Map map = (Map) list.get( i );
			mapValues.put( map.get( "TMPL_ATTR_FK" ) + "", map.get( "ATTR_VALUE" ) + "" );
		}

		return mapValues;
	}

	public boolean hasReportType(Long tmpl_Id, String dataType_id)
	{
		String hql = "from TemplateAttribute a where a.template.templateId = ? and a.dataType.typeId = ? and a.isValid = 1 ";
		List list = find( hql, tmpl_Id, dataType_id );
		return list.size() > 0;
	}

	public List<Log> getSheetLogs(Long mastInsId)
	{
		StringBuffer sbSql = new StringBuffer();

		sbSql.append( " select top 15 * " );
		sbSql.append( " from SYS_D_LOG " );
		sbSql.append( " where MAST_INS_FK = ? and LOG_CATE_FK = '10' and IS_VALID = 1 order by CREATE_DATE DESC " );

		List list = sqlQuery( sbSql.toString(), Log.class, mastInsId );
		return list;
	}

	public String getUsersByCateId(String cateId)
	{
		StringBuffer sbSQL = new StringBuffer();
		sbSQL.append( " select EMPLOYEE_FK as USER_FK from PDM_D_AUTH_EMP " );
		sbSQL.append( " where IS_VALID = 1 AND CATE_FK = " );
		sbSQL.append( cateId );
		sbSQL.append( " UNION ALL " );
		sbSQL.append( " select USER_FK from PDM_R_USER_ROLE " );
		sbSQL.append( " where IS_VALID = 1 and CATE_FK = " );
		sbSQL.append( cateId );
		List list = sqlQuery( sbSQL.toString(), "USER_FK" );

		String userIds = "";
		if( list.size() > 0 ) {
			for( int i = 0; i < list.size(); i++ ) {
				Map map = (Map) list.get( i );
				if( i != 0 ) userIds = userIds + ",";
				userIds = userIds + map.get( "USER_FK" );
			}
		}
		return userIds;
	}
}
